﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Data.OleDb;
using ADOX;
using System.Data;
using System.Data.Common;

namespace StateGrid.MDBservice
{
    public class TemporaryDataFile
    {
        private string path;
        private OleDbConnection connection;
        private CatalogClass catalogObject;

        private List<ColumnInfo> columnList;
        private Dictionary<string, Dictionary<string, string[]>> indexTable;

        public TemporaryDataFile(string source, List<ColumnInfo> schema)
        {
            string dirName = Path.GetDirectoryName(source);
            string baseName = Path.GetFileNameWithoutExtension(source);
            path = Path.Combine(dirName, baseName + "-" + Guid.NewGuid().ToString() + ".mdb");
            
            catalogObject = new CatalogClass();
            catalogObject.Create(@"Provider=Microsoft.Jet.OleDb.4.0;Data Source=" + path);

            connection = new OleDbConnection(@"Provider=Microsoft.Jet.OleDb.4.0;Data Source=" + path);
            connection.Open();

            columnList = schema;
        }

        public void CreateMainTable(string name)
        {
            TableClass tableObject = new TableClass();
            tableObject.Name = name;

            foreach (ColumnInfo column in columnList)
            {
                switch (column.Type)
                {
                    case ColumnInfo.ColumnType.BOOLEAN:
                        tableObject.Columns.Append(column.LocalName, DataTypeEnum.adBoolean);
                        break;
                    case ColumnInfo.ColumnType.DATE:
                        tableObject.Columns.Append(column.LocalName, DataTypeEnum.adDate);;
                        break;
                    case ColumnInfo.ColumnType.INTEGER:
                        tableObject.Columns.Append(column.LocalName, DataTypeEnum.adInteger);;
                        break;
                    case ColumnInfo.ColumnType.STRING:
                        if (column.Length <= 0 || column.Length > 255)
                            tableObject.Columns.Append(column.LocalName, DataTypeEnum.adLongVarWChar);
                        else
                            tableObject.Columns.Append(column.LocalName, DataTypeEnum.adVarWChar, (int)column.Length);
                        break;
                }

                if (column.Type != ColumnInfo.ColumnType.BOOLEAN)
                    tableObject.Columns[column.LocalName].Attributes = ColumnAttributesEnum.adColNullable;
            }

            catalogObject.Tables.Append(tableObject);

            connection.Close();
            connection.Open();
        }

        public void RenameTable(string oldName, string newName)
        {
            catalogObject.Tables[oldName].Name = newName;

            connection.Close();
            connection.Open();
        }

        public void DeleteTable(string tableName)
        {
            catalogObject.Tables.Delete(tableName);

            connection.Close();
            connection.Open();
        }

        public void CreateIndex(string tableName, string indexName, params string[] fields)
        {
            IndexClass index = new IndexClass();
            index.Name = indexName;
            index.IndexNulls = AllowNullsEnum.adIndexNullsAllow;
            index.Unique = false;
            foreach (string field in fields)
                index.Columns.Append(field);
            catalogObject.Tables[tableName].Indexes.Append(index);

            connection.Close();
            connection.Open();
        }

        public bool InsertRecord(string tableName, IList<string> fields, IList<object> values)
        {
            OleDbCommand cmd = new OleDbCommand();
            cmd.CommandText = String.Format("insert into [{0}] ({1}) values ({2})",
                                            tableName,
                                            string.Join(",", fields.ToArray()),
                                            string.Join(",", Enumerable.Repeat<char>('?', fields.Count))
                                            );
            cmd.Connection = connection;

            for (int i = 0; i < values.Count; ++i)
                cmd.Parameters.AddWithValue(i.ToString(), values[i]);

            cmd.ExecuteNonQuery();

            return true;
        }

        public DbDataReader GetTableReader(string tableName)
        {
            OleDbCommand cmd = new OleDbCommand(String.Format("select * from [{0}]", tableName), connection);
            return cmd.ExecuteReader();
        }

        public int GetMatchCounts(string tableName, string indexName, params object[] values)
        {
            return 0;
        }
    }
}
